package org.mics.hikariMonitor.repository;

import java.util.List;
import java.util.Map;

import javax.transaction.Transactional;

import org.mics.hikariMonitor.entity.HikariMonitorData;
import org.mics.jpa.repository.BaseRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

/**
 * hikari监听数据
 * @author mics
 * @date 2022-7-21
 * @version  1.0
 */
@Repository
public interface HikariMonitorDataRepository extends BaseRepository<HikariMonitorData> {

	@Transactional
	@Modifying
	@Query(value = "delete from t_hikari_monitor_data where hour(TIMEDIFF(now(),create_date)) > 24",nativeQuery = true)
	void removeOverdueData();

	@Query(value = "SELECT DATE_FORMAT(create_date, '%Y%m%d%H') as dateStr,convert(sum(active_connection)/count(1),DECIMAL(15,2)) as activeConnection,convert(SUM(idle_connection) / count(1),DECIMAL(15,2)) as idleConnection,convert(SUM(thread_awaiting_connection) / count(1),DECIMAL(15,2)) as threadAwaitingConnection,convert(SUM(total_connection) / count(1),DECIMAL(15,2)) as totalConnection FROM t_hikari_monitor_data GROUP BY DATE_FORMAT(create_date, '%Y%m%d%H')",nativeQuery = true)
	List<Map<String,Object>> listByHour();
	
	@Query(value = "SELECT DATE_FORMAT(create_date, '%Y%m%d%H%i') as dateStr,convert(sum(active_connection)/count(1),DECIMAL(15,2)) as activeConnection,convert(SUM(idle_connection) / count(1),DECIMAL(15,2)) as idleConnection,convert(SUM(thread_awaiting_connection) / count(1),DECIMAL(15,2)) as threadAwaitingConnection ,convert(SUM(total_connection) / count(1),DECIMAL(15,2)) as totalConnection FROM t_hikari_monitor_data where DATE_FORMAT(create_date, '%Y%m%d%H') = :dateStr GROUP BY DATE_FORMAT(create_date, '%Y%m%d%H%i')",nativeQuery = true)
	List<Map<String,Object>> listByMinite(@Param("dateStr") String dateStr);

}
